import pandas as pd
import datetime
import os
from matplotlib import ticker
import matplotlib.pyplot as plt
import xlwings as xw

# 显示中文
plt.rcParams['font.sans-serif'] = ['Microsoft Sans Serif']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 得到今天的日期并且只保留月日
today = datetime.date.today().strftime("%m-%d")
print(today)

path = '/Users/user/PycharmProjects/text1/已完成工具例子区/复测/IT_Retest_Report.xlsx'
df1 = pd.read_excel(path, sheet_name='SA-IT-LOWC', header=0, usecols=['Type', 'RR'])

# 找到type列中，含有Line的数据行
df1 = df1.loc[df1['Type'].str.contains('Line')]
df1 = df1.rename(columns={'RR': f'{today}'})
df1.set_index('Type', inplace=True)

# 得到第二个df,拼接重新输出
df2 = pd.read_excel(path, sheet_name=2, header=0)
df2.set_index('Type', inplace=True)
list1 = [df2, df1]
df = pd.concat(list1, axis=1)
print(df)
# with pd.ExcelWriter(path, mode='a') as writer:
#     df.to_excel(writer, sheet_name='Trend')  #存储到旧表分表内
with pd.ExcelWriter(path, engine='openpyxl', mode='a') as writer:
    df.to_excel(writer, sheet_name=f'Trend_PLot{today}')
clo = df.columns.values.tolist()
index = df.index.values.tolist()

# 画图
fig, ax = plt.subplots()
for i in index:
    ax.plot(clo, df.loc[i, :], label=i)
    for x, y in zip(clo, df.loc[i, :]):
        ax.text(x, y, '{:.2%}'.format(y), ha="center", va="bottom", fontsize=6)
plt.legend(bbox_to_anchor=(1, 1))  # 显示图例，确定位置
(1, 0)  # 右下角
(0, 1)  # 左上角
(0.5, 0.5)  # 中间
(0.4, 0.4)  # 中间往左下一点

ax.set_title('SA_IT_Lowc_Retest')
ax.set_ylabel('')
ax.set_xlabel('')

# y轴的数据上限下限
ax.set_ylim(0.01, 0.09)
ax.axhline(y=0.04, xmin=0, xmax=1, color='black', ls=':', alpha=1)
ax.text(x=-1.2, y=0.04, s='Line of reference', fontsize=10, color='red')

# fmt = '%.4f%%' # 设置百分数格式
# yticks = ticker.FormatStrFormatter(fmt) # 创建百分数格式化对象
# ax.yaxis.set_major_formatter(yticks) # 设置y轴格式
# y轴的数据上限下限
ax.set_ylim(0.01, 0.09)
# 设置百分数
ax.yaxis.set_major_formatter(ticker.PercentFormatter(xmax=1, decimals=1))
# plt.show()
# fig.savefig('ff.jpg', dpi=900, pad_inches=1, bbox_inches="tight")

# 将图表插入到excel中
app = xw.App(visible=False, add_book=False)
wb = app.books.open(path)
sheet = wb.sheets[f'Trend_PLot{today}']  # 选择第3个工作表
sheet.pictures.add(fig)  # 插入图表

wb.save()
wb.close()
app.quit()
